DB2

Querying Overview

Qarbine utilizes the IBM DB2 ODBC library to access data within the DB2 Cloud instance. For querying the language used is PL/SQL. A SQL reference starting point is at
https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/SQL-Queries-and-Subqueries.html

The general format of most queries is

SELECT
FROM
WHERE
ORDER BY

Qarbine Virtual Queries

There are a few convenience queries which are mainly DBA oriented depending on the database. These queries are recognized by the Qarbine driver and provide common database information. Any database and schema set in the data service definition constrains what is returned.

Query	Description
list tables	Return a list of tables.

See the “DBA Productivity” section of the online documentation for more details. It has DB2 SQL centric queries to retrieve table details for example.

Interacting with JSON Data

Overview

JSON data is generally stored as a BLOB. The function JSON2BSON and BSON2JSON are common ones for inserting and retrieving content. Shown below is a query on a table containing JSON oriented data in the PRODUCT_DETAILS column and its results.

  

The JSON_VAL function can be used to extract individual JSON object field values. A page with information on various JSON interactions is at
https://www.ibm.com/docs/en/db2-for-zos/12?topic=json-working-documents-by-using-sql

Qarbine Data Source Queries

Consider a table with the following creation snippet.

CREATE TABLE jsonSales (
    sale_id INTEGER NOT NULL PRIMARY KEY,
    sale_date DATE,
    customer_name VARCHAR(100),
    …
    product_details BLOB
);

Below is a query accessing the table.

select * from jsonSALES limit 1

A result row is shown below.

  

Its details are shown below,

  

The PRODUCT_DETAILS BLOB column value’s data array contains 181 integers.
An adjusted query is shown below.
Below is a query accessing the table.

select *, SYSTOOLS.BSON2JSON(PRODUCT_DETAILS) as PRODUCT
from jsonSALES 
limit 1

The new details are shown below.

  

The PRODUCT column is the JSON string from the stored BLOB.

In this simple query the PRODUCT_DETAILS BLOB column value can be removed from the processed results using specific SELECT columns or a Qarbine pragma. The former removes is from the DB2 answer set sent to the Qarbine host. The latter removes the answer set rows’ field at the Qarbine host level.

#pragma deleteFields PRODUCT_DETAILS

In some cases your analytics wants to interact with a real JSON object and not a simple string. In this case another Qarbine pragma can be used as shown below.

#pragma convertToObject PRODUCT

A final query specification is shown below.

#pragma convertToObject PRODUCT
#pragma deleteFields PRODUCT_DETAILS
select *, SYSTOOLS.BSON2JSON(PRODUCT_DETAILS) as PRODUCT
from jsonSALES 
limit 1

A sample answer set row is shown below.

  

Below is a query which uses the JSON_VAL function.

#pragma convertToObject PRODUCT
#pragma deleteFields PRODUCT_DETAILS
select *, SYSTOOLS.BSON2JSON(PRODUCT_DETAILS) as PRODUCT
, JSON_VAL(PRODUCT_DETAILS, 'model', 's:50') as model
from jsonSALES 
order by sale_date asc

A sample answer set row is shown below.

  

Troubleshooting

If the query used within Qarbine is not yielding the anticipated results, then the IBM DB2 Cloud SQL console can be used to test queries.

Sign on to your IBM DB2 instance.

Next, navigate to the DB2 console.

Select the SQL option highlighted below.

  

Review the tables and views to confirm them relative to your query.

  

Enter your SQL and click “Run all”.

  

Review the feedback and compare it with that presented by Qarbine.